﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Runtime;
using System.Reflection;

namespace System.Data
{
    /// <summary>
    /// 配合数据库使用的工具
    /// </summary>
    public class SQLUtilies
    {
        /// <summary>
        /// 快速填充Key值（使用 default）
        /// </summary>
        /// <param name="keys"></param>
        /// <returns></returns>
        public static Dictionary<TKey, TValue> CreateRowDataKeys<TKey, TValue>(IEnumerable<TKey> keys)
        {
            Dictionary<TKey, TValue> dict = new Dictionary<TKey, TValue>();
            foreach (TKey key in keys)
            {
                dict.Add(key, default(TValue));
            }

            return dict;
        }
    }

    /// <summary>
    /// 字典扩展类
    /// </summary>
    public static class DictionaryExtension
    {
        /// <summary>
        /// 获取字典的Key值字符串
        /// </summary>
        /// <param name="dict"></param>
        /// <param name="spaceChar"></param>
        /// <returns></returns>
        public static string GetKeys(this IDictionary dict, string spaceChar = ",")
        {
            string r = "";
            foreach (object key in dict.Keys)
            {
                r += key.ToString() + spaceChar;
            }

            // 直接去除最后一个符号
            r = r.Remove(r.Length - spaceChar.Length, spaceChar.Length);

            return r;
        }

        /// <summary>
        /// 获取字典的所有Value值，使用指定符号构造成字符串
        /// <para>会跳过空值</para>
        /// </summary>
        /// <param name="dict"></param>
        /// <param name="spaceChar"></param>
        /// <returns></returns>
        public static string GetValues(this IDictionary dict, string spaceChar = ",")
        {
            string r = "";
            foreach (object value in dict.Values)
            {
                //有可能出现为空的情况
                if (value == null || string.IsNullOrEmpty(value.ToString()) || string.IsNullOrWhiteSpace(value.ToString()))
                {
                    continue;
                }
                r += "'" + value.ToString() + "'" + spaceChar;
            }

            // 直接去除最后一个符号
            r = r.Remove(r.Length - spaceChar.Length, spaceChar.Length);

            return r;
        }

        /// <summary>
        /// 获取单个字典对象进行Update时的sql指令
        /// </summary>
        /// <param name="dict"></param>
        /// <param name="spaceChar"></param>
        /// <returns></returns>
        public static string GetSQLUpdateSet(this IDictionary dict, string spaceChar = ",")
        {
            string r = "";
            foreach (object key in dict.Keys)
            {
                object value = dict[key];
                //有可能出现为空的情况
                if (value == null || string.IsNullOrEmpty(value.ToString()) || string.IsNullOrWhiteSpace(value.ToString()))
                {
                    continue;
                }

                r += $"{key} = '{value}'{spaceChar}";
            }

            // 直接去除最后一个符号
            r = r.Remove(r.Length - spaceChar.Length, spaceChar.Length);

            return r;
        }

        /// <summary>
        /// 获取一个新字典对象
        /// <para>可以用来精简字典</para>
        /// <para>当 newKey 设置为空时，会进行字典的复制</para>
        /// </summary>
        /// <typeparam name="TKey"></typeparam>
        /// <typeparam name="TValue"></typeparam>
        /// <param name="source"></param>
        /// <param name="newKey"></param>
        /// <returns></returns>
        public static IDictionary<TKey, TValue> ReSet<TKey, TValue>(this IDictionary<TKey, TValue> source, TKey[] newKey = null)
        {
            Dictionary<TKey, TValue> dict = new Dictionary<TKey, TValue>();

            if (newKey == null || newKey.Length <= 0)
            {
                foreach (KeyValuePair<TKey, TValue> kp in source)
                {
                    dict.Add(kp.Key, kp.Value);
                }
            }
            else
            {
                foreach (TKey key in newKey)
                {
                    if (source.ContainsKey(key))
                    {
                        dict.Add(key, source[key]);
                    }
                }
            }

            return dict;
        }
    }

    /// <summary>
    /// 获取 SQL 命令的拓展类
    /// </summary>
    public static class ToSQLCmdExtension
    {
        /// <summary>
        /// 负责类型转换的字典
        /// </summary>
        public static Dictionary<Type, string> DbTypeConvert = new Dictionary<Type, string>() {
            {typeof(string),"VARCHAR(255)" },
            {typeof(int),"NUMERIC" },
            {typeof(uint),"NUMERIC" },
            {typeof(DateTime),"DATE" }
        };

        /// <summary>
        /// 获取某类型的表创建构造语句
        /// </summary>
        /// <param name="ty"></param>
        /// <param name="newDbTypeConvert"></param>
        /// <returns></returns>
        public static string[] GetCreateTableSQLCmd(this Type ty, IDictionary<Type, string> newDbTypeConvert = null)
        {
            // 传入了类型控制字典时，对字典进行更新
            // 复制一份字典，不会修改默认的类型转换字典
            // 原字典没有的进行添加，原字典有的进行更新
            IDictionary<Type, string> useDbTypeConvert = DbTypeConvert;
            if (newDbTypeConvert != null && newDbTypeConvert.Count > 0)
            {
                useDbTypeConvert = DbTypeConvert.ReSet();
                foreach (KeyValuePair<Type, string> kv in newDbTypeConvert)
                {
                    if (useDbTypeConvert.ContainsKey(kv.Key))
                    {
                        useDbTypeConvert[kv.Key] = kv.Value;
                    }
                    else
                    {
                        useDbTypeConvert.Add(kv.Key, kv.Value);
                    }
                }
            }

            List<string> results = new List<string>();

            // 遍历字段
            foreach(FieldInfo fi in ty.GetFields())
            {
                DbSettingAttribute setting = (DbSettingAttribute)fi.GetCustomAttribute(typeof(DbSettingAttribute));

                if (setting == null)
                {
                    continue;
                }

                results.Add(fi.GetCreateTableSQLCmd(useDbTypeConvert));
            }

            // 遍历属性
            foreach (PropertyInfo pi in ty.GetProperties())
            {
                DbSettingAttribute setting = (DbSettingAttribute)pi.GetCustomAttribute(typeof(DbSettingAttribute));

                if (setting == null)
                {
                    continue;
                }

                results.Add(pi.GetCreateTableSQLCmd(useDbTypeConvert));
            }

            useDbTypeConvert = null;
            return results.ToArray();
        }

        /// <summary>
        /// 获取字段的构造命令形式
        /// </summary>
        /// <param name="fi"></param>
        /// <param name="newDbTypeConvert"></param>
        /// <returns></returns>
        public static string GetCreateTableSQLCmd(this FieldInfo fi, IDictionary<Type, string> newDbTypeConvert = null)
        {
            DbSettingAttribute setting = (DbSettingAttribute)fi.GetCustomAttribute(typeof(DbSettingAttribute));

            if (setting == null)
            {
                return null;
            }

            string dbType = setting.dbType;

            if (newDbTypeConvert != null && newDbTypeConvert.ContainsKey(fi.FieldType) && !setting.IsUseType)
            {
                dbType = newDbTypeConvert[fi.FieldType];
            }


            string result =
                $"{fi.Name} {dbType} " +
                $"{(setting.IsPrimary ? " PRIMARY KEY " : " ")} " +
                $"{(setting.IsNotNull ? " NOT NULL " : " ")} " +
                $"{(setting.DefaultValue!=null? $" DEFAULT ({setting.DefaultValue}) " : " " ) }" +
                $"{(setting.IsForeign? $" REFERENCES ({setting.ForeignReference}) " : " ") }";

            return result;
        }

        /// <summary>
        /// 获取属性的构造命令形式
        /// </summary>
        /// <param name="pi"></param>
        /// <param name="newDbTypeConvert"></param>
        /// <returns></returns>
        public static string GetCreateTableSQLCmd(this PropertyInfo pi, IDictionary<Type, string> newDbTypeConvert = null)
        {
            DbSettingAttribute setting = (DbSettingAttribute)pi.GetCustomAttribute(typeof(DbSettingAttribute));

            if (setting == null)
            {
                return null;
            }

            string dbType = setting.dbType;

            if (newDbTypeConvert != null && newDbTypeConvert.ContainsKey(pi.PropertyType) && !setting.IsUseType)
            {
                dbType = newDbTypeConvert[pi.PropertyType];
            }


            string result =
                $"{pi.Name} {dbType} " +
                $"{(setting.IsPrimary ? " PRIMARY KEY " : " ")} " +
                $"{(setting.IsNotNull ? " NOT NULL " : " ")} " +
                $"{(setting.DefaultValue != null ? $" DEFAULT ({setting.DefaultValue}) " : " ") }" +
                $"{(setting.IsForeign ? $" REFERENCES ({setting.ForeignReference}) " : " ") }";

            return result;
        }
    }

}
